﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using MISAPP.Classify.DataAccess;

namespace MISAPP.Classify.Service
{

    public partial class Squared_Excel
    {
        int wowkSheetsCount = 0;
        Microsoft.Office.Interop.Excel.Sheets wrkshts = null;
        /*
        Dictionary<string
            , Microsoft.Office.Interop.Excel.Worksheet> dictSht = null;
         */ 
        public List<Squared> SquaredValue = null;
        protected Dictionary<string, string> squaredCaption = null;
        //protected Dictionary<string, string> otherCaption = null;
        public Squared_Excel(Dictionary<string, string> p_dict)
        {
            squaredCaption = p_dict;
            //otherCaption = p_other;
            exlAPP = new Microsoft.Office.Interop.Excel.Application();
        }

        public Squared_Excel()
        {
            exlAPP = new Microsoft.Office.Interop.Excel.Application();
        }

        protected Microsoft.Office.Interop.Excel.Application exlAPP = null;

        public bool ReadUploadCreditCardCount(string p_filePath, string p_shtName)
        {
            bool result = false;
            try
            {
                openFile(p_filePath);
                Microsoft.Office.Interop.Excel.Worksheet wsht =
                    getSheetObject(p_shtName) as Microsoft.Office.Interop.Excel.Worksheet;
                //Range xxx = wsht.Cells[2, 2] as Range;
                Microsoft.Office.Interop.Excel.Range rng1 = wsht.get_Range("B1:BB1", Type.Missing);
                Microsoft.Office.Interop.Excel.Range rng2 = wsht.get_Range("B2:BB2", Type.Missing);
                Microsoft.Office.Interop.Excel.Range rng3 = wsht.get_Range("B3:BB3", Type.Missing);
                object[,] cardTypes = rng1.Value2 as object[,];
                object[,] circulates = rng2.Value2 as object[,];
                object[,] effectives = rng3.Value2 as object[,];
                Service_CreditCardCount credit = new Service_CreditCardCount();
                result = credit.Insert(cardTypes, circulates, effectives, p_shtName);

                //2011-11-21 加入採購卡資訊
                rng1 = wsht.get_Range("B5:T5", Type.Missing);
                rng2 = wsht.get_Range("B6:T6", Type.Missing);
                rng3 = wsht.get_Range("B7:T7", Type.Missing);
                cardTypes = rng1.Value2 as object[,];
                circulates = rng2.Value2 as object[,];
                effectives = rng3.Value2 as object[,];                
                result = credit.Insert(cardTypes, circulates, effectives, p_shtName,false);

                closeWorkBooksWithNoSave(p_filePath);
            }
            catch (Exception ex)
            {
                Utility.ReleaseCOMObject(exlAPP);
            }
            return result;
        }

        public bool ReadUploadBonus(string p_filePath, List<Squared> p_list)
        {
            bool ok = true;
            string rngValue = string.Empty;
            try
            {
                SquaredValue = new List<Squared>();
                openFile(p_filePath);
                foreach (Squared seq in p_list)
                {
                    Microsoft.Office.Interop.Excel.Worksheet wsht = getSheetObject(seq.Sheet) as
                        Microsoft.Office.Interop.Excel.Worksheet;
                    if (wsht != null)
                    {
                        foreach (string strRng in seq.Ranges)
                        {
                        Microsoft.Office.Interop.Excel.Range rng = wsht.get_Range(strRng, Type.Missing);
                            rngValue = rng.Value2 == null ? string.Empty
                                : rng.Value2.ToString();

                            SquaredValue.Add(new Squared(strRng, string.Empty
                                , rngValue, rng.Row, seq.Sheet, string.Empty));
                        }
                    }
                }
                closeWorkBooksWithNoSave(p_filePath);
            }
            catch (Exception ex)
            {
                ok = false;
                Utility.ReleaseCOMObject(exlAPP);
                throw new Exception("Exception Read UploadFile ERROR :" + ex.ToString());                
            }
            finally
            {

            }
            return ok;
        }

        /// <summary>
        /// 給2011版本來使用
        /// </summary>
        /// <param name="p_filePath"></param>
        /// <param name="p_list"></param>
        /// <param name="p_team"></param>
        /// <returns></returns>
        public bool ReadUploadFile(string p_filePath, List<Squared> p_list
            , string p_team,string p_user)
        {
            bool ok = false;
            int pos = 0;
            string rngValue = string.Empty;
            try
            {
                SquaredValue = new List<Squared>();
                openFile(p_filePath);
                foreach (Squared seq in p_list)
                { 
                    Microsoft.Office.Interop.Excel.Worksheet wsht = getSheetObject(seq.Sheet) as
                        Microsoft.Office.Interop.Excel.Worksheet;
                    pos = 0;
                    foreach (string strRng in seq.Ranges)
                    {
                        if (seq.IsRange(strRng))
                        {
                            Microsoft.Office.Interop.Excel.Range rng =
                                wsht.get_Range(strRng, Type.Missing);
                            rngValue = rng.Value2 == null 
                                        ? "" : rng.Value2.ToString();
                            SquaredValue.Add(new Squared(seq.Sheet, p_team, p_user
                                , pos.ToString(), rngValue
                                , string.Empty, null));
                        }                        
                        pos++;
                    }
                }
                ok = true;
            }
            catch (Exception ex)
            {
                ok = false;
                throw new Exception("Exception Read UploadFile ERROR :" + ex.ToString());
                Utility.ReleaseCOMObject(exlAPP);            
            }
            return ok;
        }

        public bool ReadUploadFile(string p_filePath, List<Squared> p_list
            , string p_usr, ref System.Data.DataTable p_dt)
        {
            bool ok = false;
            string rngValue = string.Empty;
            string comment = string.Empty;
            try
            {
                SquaredValue = new List<Squared>();
                openFile(p_filePath);
                foreach (Squared seq in p_list)
                {
                    Microsoft.Office.Interop.Excel.Worksheet wsht = getSheetObject(seq.Sheet) as
                        Microsoft.Office.Interop.Excel.Worksheet;
                    if (wsht != null)
                    {
                        foreach (string strRng in seq.Ranges)
                        {
                            Microsoft.Office.Interop.Excel.Range rng = wsht.get_Range(strRng, Type.Missing);
                            rngValue = rng.Value2 == null ? "0" : rng.Value2.ToString();
                            if (isNumeric(rngValue))
                            {
                                comment = string.Empty;
                            }
                            else
                            {
                                comment = rngValue;
                                rngValue = "0";
                            }
                            DataRow row = p_dt.NewRow();
                            row["MISGroup"] = strRng;
                            row["MISItem"] = "";
                            row["Amount"] = rngValue;
                            row["user_id"] = p_usr;
                            row["ROW_POS"] = rng.Row;
                            row["SHEETNAME"] = seq.Sheet;
                            row["COMMENT"] = comment;
                            p_dt.Rows.Add(row);
                        }
                    }
                }
                closeWorkBooksWithNoSave(p_filePath);
                ok = true;
            }
            catch (Exception ex)
            {
                ok = false;
                Utility.ReleaseCOMObject(exlAPP);
                throw new Exception("Exception Read UploadFile ERROR :" + ex.ToString());
            }
            return ok;
        }

        public bool ReadUploadFile(string p_filePath, List<Squared> p_list)
        {
            bool ok = false;
            string rngValue = string.Empty;
            string comment = string.Empty;
            try
            {
                SquaredValue = new List<Squared>();
                openFile(p_filePath);
                foreach (Squared seq in p_list)
                {
                    Microsoft.Office.Interop.Excel.Worksheet wsht = getSheetObject(seq.Sheet) as
                        Microsoft.Office.Interop.Excel.Worksheet;
                    if (wsht != null)
                    {
                        foreach (string strRng in seq.Ranges)
                        {
                            Microsoft.Office.Interop.Excel.Range rng = wsht.get_Range(strRng, Type.Missing);
                            //2013-01-22 這一段就給空白，未來不需要用到string misItem =  getCaption(strRng, seq.Sheet);
                            rngValue = rng.Value2 == null ? "0" : rng.Value2.ToString();
                            //如果該cell 是數字,則 comment 就給空白，反之是該cell 給0，comment 給該cell 值
                            if (isNumeric(rngValue))
                            {
                                comment = string.Empty;
                            }
                            else
                            {
                                comment = rngValue;
                                rngValue = "0";
                            }
                            //SquaredValue.Add(new Squared(strRng, misItem
                            //    , rngValue, rng.Row, seq.Sheet, comment));
                            SquaredValue.Add(new Squared(strRng, ""
                                , rngValue, rng.Row, seq.Sheet, comment));
                        }
                    }
                }
                closeWorkBooksWithNoSave(p_filePath);
                ok = true;
            }
            catch (Exception ex)
            {
                ok = false;
                Utility.ReleaseCOMObject(exlAPP);
                throw new Exception("Exception Read UploadFile ERROR :" + ex.ToString());                
            }
            return ok;
        }

        protected void closeWorkBooksWithNoSave(string p_filePath)
        {
            for (int i = 1; i <= exlAPP.Workbooks.Count; i++)
            {
                object saveChange = false;
                exlAPP.Workbooks[i].Close(saveChange
                    , System.IO.Path.GetFileName(p_filePath), Type.Missing);
            }
        }

        protected void openFile(string p_filePath)
        {
            try
            {
                object oMissing = System.Reflection.Missing.Value;

                if (!System.IO.File.Exists(p_filePath))
                {
                    throw new Exception("上存路徑沒有該暂存檔案");
                }

                object updatelink = false;
                exlAPP.Workbooks.Open(p_filePath, updatelink, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing
                    , oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
                wowkSheetsCount = exlAPP.Sheets.Count;
                wrkshts = exlAPP.Sheets 
                    as Microsoft.Office.Interop.Excel.Sheets;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("無法開啟excel 檔案:{0}", ex.Message));

            }

        }

        protected object getSheetObject(string p_sheetName
            , Microsoft.Office.Interop.Excel.Worksheet p_sht)
        {
            return p_sht.Name.Equals(p_sheetName)
                    ? p_sht : getSheetObject(p_sheetName);
        }

        protected object getSheetObject(string p_sheetName)
        {
            Microsoft.Office.Interop.Excel.Worksheet ws = null,found = null;
            for (int i = 1; i <= wowkSheetsCount; i++)
            {
                /*
                ws = (Microsoft.Office.Interop.Excel.Worksheet)
                        exlAPP.Worksheets.get_Item(i);
                */
                ws = wrkshts.get_Item(i) as
                    Microsoft.Office.Interop.Excel.Worksheet;
                if (ws.Name.Trim().Equals(p_sheetName))
                {
                    found = ws;
                    break;
                }
            }
            return found ;
        }
       
        protected string getCaption(string p_rng, string p_sheet)
        {
            //Dictionary<string, string> dict = p_sheet.Substring(0, 1) == "稅" 
            //    ? squaredCaption : otherCaption ;
            Dictionary<string, string> dict = squaredCaption;
            string rtnValue = string.Empty;
            int i = 1;
            while (!dict.TryGetValue(p_rng.Substring(0, i), out rtnValue))
            {
                i++;
                if (i == p_rng.Length)
                {
                    break;
                }
            }
            return rtnValue;
        }

        public bool GetBonusResult(string p_templatePath, Service_Bonus p_bonus
            , bool p_l)
        {
            string[] shts = Utility.GetSheets(p_bonus.GetTeam());
            string[] dates = p_bonus.GetTeam().Equals("BONUS")
                ? new string[] { "B1", "A2", "A2", "A2", "A2" }
                : null;
            return getBonusResult(p_templatePath, p_bonus
                , shts, dates,p_l);        
        }


        public bool GetBonusResult(string p_templatePath, Service_Bonus p_bonus)
        {
            string[] shts = Utility.GetSheets(p_bonus.GetTeam());
            string[] dates = p_bonus.GetTeam().Equals("BONUS") 
                ? new string[] { "B1", "A2", "A2", "A2", "A2" }
                :null;
            return GetBonusResult(p_templatePath, p_bonus
                , shts , dates);
        }

        protected bool getBonusResult(string p_templatePath, Service_Bonus p_bonus
            , string[] p_shts, string[] p_dates,bool p_l)
        {
            openFile(p_templatePath);
            bool ok = false;
            string other = string.Empty;
            Microsoft.Office.Interop.Excel.Range rng = null;
            try
            {
                ok = true;
                System.Data.DataTable dt = p_bonus.GetDataBySheet(p_shts);
                Microsoft.Office.Interop.Excel.Worksheet wsht = 
                    getSheetObject(dt.Rows[0][2].ToString()) as
                          Microsoft.Office.Interop.Excel.Worksheet;
                foreach (DataRow row in dt.Rows)
                {
                    wsht = getSheetObject(row[2].ToString(), wsht) as
                            Microsoft.Office.Interop.Excel.Worksheet;
                    rng = wsht.get_Range(row["MISGroup"].ToString()
                        , Type.Missing);
                    rng.Value2 = row["cell_value"].ToString();
                }
            }
            catch (Exception ex)
            {
                ok = false;
                Utility.ReleaseCOMObject(exlAPP);
                throw new Exception("產生EXCEL 檔錯誤 ERROR :" + ex.ToString());                
            }
            return ok;
        }

        protected bool GetBonusResult(string p_templatePath, Service_Bonus p_bonus
            , string[] p_shts, string[] p_dates)
        {
            openFile(p_templatePath);
            bool ok = false;
            string other = string.Empty;
            Microsoft.Office.Interop.Excel.Range rng = null;
            try
            {
                int i = 0;
                foreach (string sht in p_shts)
                {
                    System.Data.DataTable dt = p_bonus.GetDataBySheet(sht);
                    Microsoft.Office.Interop.Excel.Worksheet wsht = getSheetObject(sht) as
                           Microsoft.Office.Interop.Excel.Worksheet;

                    if (p_dates != null)
                    {
                        setVersionDate(wsht, p_dates[i]);
                    }
                    
                    i++;
                    foreach (DataRow row in dt.Rows)
                    {
                        rng = wsht.get_Range(row["MISGroup"].ToString()
                               , Type.Missing);
                        rng.Value2 = row["cell_value"].ToString();
                    }
                }
                Microsoft.Office.Interop.Excel.Workbook wbk = exlAPP.Workbooks[1];
                wbk.Save();
                exlAPP.Quit();
                ok = true;
            }
            catch (Exception ex)
            {
                ok = false;
                Utility.ReleaseCOMObject(exlAPP);
                throw new Exception("產生EXCEL 檔錯誤 ERROR :" + ex.ToString());                
            }
            return ok;
        }
        /*
        public bool GetBonusResult(string p_templatePath, Service_Bonus p_bonus)
        {
            return GetBonusResult(p_templatePath, p_bonus
                , new string[] { "年獎", "盈餘空間", "101員額", "100員額", "100年差旅費" }
                , new string[] { "B1", "A2", "A2", "A2", "A2" });
        }
        */

        public bool GetSquaredResult(string p_templatePath, Service_Squared p_servSeq)
        {
            return GetSquaredResult(p_templatePath, p_servSeq, string.Empty);
        }

        public bool GetSquaredResult(string p_templatePath, Service_Squared p_servSeq, bool p_)
        {
            List<DataRow> row18, row19;
            openFile(p_templatePath);
            DA_Squared da = new DA_Squared();
            System.Data.DataTable DT = da.GetSquaredData();
            Microsoft.Office.Interop.Excel.Worksheet wsht = null;
            Microsoft.Office.Interop.Excel.Range rng = null;
            row18 = new List<DataRow>();
            row19 = new List<DataRow>();
            wsht = getSheetObject(DT.Rows[0][2].ToString()) as
                           Microsoft.Office.Interop.Excel.Worksheet;
            foreach (DataRow row in DT.Rows)
            {
                wsht = getSheetObject(row[2].ToString(), wsht) as
                        Microsoft.Office.Interop.Excel.Worksheet;

                //預算數、實估數 中的18 19 先拉出來
                if (isValueSheetName(wsht.Name))
                {
                    if (row[0].ToString().IndexOf("18") > 0)
                    {
                        row18.Add(row);
                    }

                    if (row[0].ToString().IndexOf("19") > 0)
                    {
                        row19.Add(row);
                    }
                }
                rng = wsht.get_Range(row["MISGroup"].ToString()
                               , Type.Missing);
                //如果是說明頁
                rng.Value2 = isCommentSheetName(wsht.Name)
                    ? row["comment"].ToString() : row["amount"].ToString();             
            }
            procRate(row18, row19);
            //設定取得資料的日期
            wsht = getSheetObject("稅前淨利彙總_預算數", wsht) as
                           Microsoft.Office.Interop.Excel.Worksheet;
            setVersionDate(wsht, "A100");
            Microsoft.Office.Interop.Excel.Workbook wbk = exlAPP.Workbooks[1];
            wbk.Save();
            exlAPP.Quit();
            return true;
        }

        void procRate(List<DataRow> p_row18, List<DataRow> p_row19)
        {
            Microsoft.Office.Interop.Excel.Worksheet wsht = null;
            Microsoft.Office.Interop.Excel.Range rng18,rng19;
            string row18Value, row19Value;
            DataRow row18,row19;
            row18 = p_row18[0] as DataRow;
            wsht = getSheetObject(row18[2].ToString()) as
                           Microsoft.Office.Interop.Excel.Worksheet;
            for (int i = 0; i < p_row18.Count; i++)
            {
                row18 = p_row18[i] as DataRow;
                row18Value = row18["amount"].ToString(); //有效稅率(%)
                row19 = p_row19[i] as DataRow;
                row19Value = row19["amount"].ToString(); //稅後損益
                wsht = getSheetObject(row18[2].ToString(), wsht) as
                           Microsoft.Office.Interop.Excel.Worksheet;
                rng18 = wsht.get_Range(row18["MISGroup"].ToString()
                       , Type.Missing);
                rng19 = wsht.get_Range(row19["MISGroup"].ToString()
                                           , Type.Missing);
                //如果都沒有輸入，就不理它
                if (isZero(row18Value) & isZero(row19Value))
                {}
                else
                {
                    //只有稅後損益
                    if (!isZero(row19Value) & isZero(row18Value))
                    {
                        rng18.FormulaR1C1 = "=(R[-1]-R[1])/R[-1]*100";
                        rng19.Value2 = row19Value;
                    }
                    //只有有效稅率(%)
                    if (isZero(row19Value) & !isZero(row18Value))
                    {
                        rng18.Value2 = row18Value;
                        rng19.FormulaR1C1 = "=R[-2]C*(100-R[-1]C)/100";
                    }
                    if (!isZero(row18Value) & !isZero(row19Value))
                    {
                        rng18.Value2 = row18Value;
                        rng19.FormulaR1C1 = "=R[-2]C*(100-R[-1]C)/100";
                    }
                }
            }

        }

        bool isValueSheetName(string p_name)
        {
            return (checkSheetName(p_name, "稅前淨利") 
                & !isCommentSheetName(p_name)) ? true : false;
        }

        bool isCommentSheetName(string p_name)
        {
            return checkSheetName(p_name, "說明");
        }

        bool checkSheetName(string p_name, string p_chkName)
        {
            return p_name.IndexOf(p_chkName) > -1 ? true : false;
        }

        public bool GetSquaredResult(string p_templatePath, Service_Squared p_servSeq, string p_after)
        {
            //要做八次，要把 template 中的檔案，產生到download 中去,給這一個session的人來download
            openFile(p_templatePath);//,"稅前淨利彙總_實估數-預算數" 
            //2013-03-12 新加入收入的工作SHEET，看2013年底是否需要再刪除
            string[] sheetArray = {"稅前淨利彙總_實估數","稅前淨利彙總_預算數"
                 ,"其它收支明細_實估數","其它收支明細_預算數","稅前淨利彙總_差異說明"
                 ,"其它收支明細_差異說明"
                 ,"102年手續收入","101年手續收入"
                 ,"101年利息收入","102年利息收入"
                                  };
            bool ok = false;
            string other = string.Empty;
            Microsoft.Office.Interop.Excel.Range rng = null;
            Microsoft.Office.Interop.Excel.Range rng15 = null;
            Microsoft.Office.Interop.Excel.Worksheet wsht = null;
            
            try
            {
                foreach (string sht in sheetArray)
                {
                    other = string.Empty;
                    
                    if (sht.Equals(sheetArray[0]))
                    {
                        other = "其它收支明細_實估數";
                    }
                    if (sht.Equals(sheetArray[1]))
                    {
                        other = "其它收支明細_預算數";
                    }
                    //2011-12-12 不要去算其它收支項目，改由EXCEL加總後試定公式來使用
                    //System.Data.DataTable dt = p_servSeq.GetDataBySheetName(sht, other, p_after);
                    System.Data.DataTable dt = p_servSeq.GetDataBySheetName(sht,string.Empty, p_after);
                    wsht = getSheetObject(sht) as
                           Microsoft.Office.Interop.Excel.Worksheet;

                    if (sht.Equals(sheetArray[1]))
                    {
                        //setVersionDate(wsht, "A2");
                        setVersionDate(wsht, "A100");
                    }

                    IEnumerable<DataRow> selectRow = null;
                    //"其它收支明細_實估數","其它收支明細_預算數"
                    if (sht.Equals(sheetArray[2]) || sht.Equals(sheetArray[3]))
                    {
                        //如果是其它收支項目的，就不要設ROW < 14 的
                        selectRow = from aa in dt.AsEnumerable()
                                    select aa;
                    }
                    else
                    {
                        //先找出ROW15之前的再找出關係人交易相關要輸入的資料
                        selectRow = from aa in dt.AsEnumerable()
                                    where (aa.Field<Int32>("ROW_POS") < 17 ||
                                    aa.Field<Int32>("ROW_POS") > 19)
                                    select aa;
                    }


                    foreach (DataRow row in selectRow)
                    {
                        rng = wsht.get_Range(row["MISGroup"].ToString()
                               , Type.Missing);
                        if (sht.IndexOf("差異說明") > 0)
                        {
                            rng.Value2 = row["comment"].ToString();
                        }
                        else
                        {
                            rng.Value2 = row["amount"].ToString();                            
                        }
                    }
                    #region 找出稅前的有效稅率(%) &稅前損益
                    if ((sht.IndexOf("差異說明") < 0) & !string.Empty.Equals(other))
                    {

                        DataRow[] select15 = getSelect15OR16(18, dt);
                        DataRow[] select16 = getSelect15OR16(19, dt);

                        string amount15, amount16;
                        for (int i = 0; i < select16.Length; i++)
                        {
                            //先檢查是否為數字
                            amount15 = select15[i]["AMOUNT"].ToString();
                            amount16 = select16[i]["AMOUNT"].ToString();
                            if (!isNumeric(amount16))
                            {
                                throw new Exception("計算稅率異常!");
                            }
                            if (!isNumeric(amount15))
                            {
                                throw new Exception("計算稅率異常!");
                            }

                            //如果都沒有輸入，就不理它
                            if (isZero(amount16) & isZero(amount15))
                            { }
                            else
                            {
                                rng = wsht.get_Range(select16[i]["MISGroup"].ToString()
                                           , Type.Missing);
                                rng15 = wsht.get_Range(select15[i]["MISGroup"].ToString()
                                           , Type.Missing);
                                //只有稅後損益
                                if (!isZero(amount16) & isZero(amount15))
                                {
                                    //rng15.FormulaR1C1 = "=ROUND((R[-1]-R[1])/R[-1]*100,0)";
                                    try
                                    {
                                        rng15.FormulaR1C1 = "=(R[-1]-R[1])/R[-1]*100";
                                        rng.Value2 = amount16;
                                    }
                                    catch (Exception ex)
                                    { 
                                    
                                    }
   
                                }
                                //只有有效稅率(%)
                                if (isZero(amount16)
                                    & !isZero(amount15))
                                {
                                    rng15.Value2 = amount15;
                                    rng.FormulaR1C1 = "=R[-2]C*(100-R[-1]C)/100";
                                }
                                if (!isZero(amount16) & !isZero(amount15))
                                {
                                    rng15.Value2 = amount15;
                                    rng.FormulaR1C1 = "=R[-2]C*(100-R[-1]C)/100";
                                }
                            }
                        }
                    }
                    #endregion 
                }
                Microsoft.Office.Interop.Excel.Workbook wbk = exlAPP.Workbooks[1];
                wbk.Save();

                wbk.Close(System.Reflection.Missing.Value, System.Reflection.Missing.Value
                    , System.Reflection.Missing.Value);
                //exlAPP.Quit();
                ok = true;
            }
            catch (Exception ex)
            {
                Utility.ReleaseCOMObject(exlAPP);
                ok = false;
                throw new Exception("產生EXCEL 檔錯誤 ERROR :" + ex.ToString());                
            }
            finally
            {
                //releaseObject(exlAPP);
            }
            return ok;
        }

        DataRow[] getSelect15OR16(int p_1516, System.Data.DataTable p_dt)
        {
            DataRow[] select = (from aa in p_dt.AsEnumerable()
                                where aa.Field<Int32>("ROW_POS") == p_1516
                                select aa).ToArray<DataRow>();
            return select;
        }

        public void ReleaseExcel()
        {
            Utility.ReleaseCOMObject(exlAPP);
        }

        bool isNumeric(string p_val)
        {
            bool ok = true;
            try
            {
                Double.Parse(p_val, System.Globalization.NumberStyles.Any);
            }
            catch (Exception ex)
            {
                ok = false;
            }
            return ok;
        }

        bool isZero(string p_val)
        {
            return Double.Parse(p_val, System.Globalization.NumberStyles.Any).Equals(0);
        }

        void setVersionDate(Microsoft.Office.Interop.Excel.Worksheet p_sht, string p_range)
        {
            Microsoft.Office.Interop.Excel.Range rng =
                p_sht.get_Range(p_range, Type.Missing);
            string strDate = string.Format("{0}年{1}月{2}日版本"
                , Convert.ToString(DateTime.Now.Year - 1911)
                , DateTime.Now.Month.ToString()
                , DateTime.Now.Day.ToString());
            rng.Value2 = strDate;
        }

        public void KPISetVersion(string p_filePath, List<Squared> p_list
            , Service_KPI p_kpi, string p_user)
        {
            openFile(p_filePath);
            string a2 = "A2";
            foreach (Squared seq in p_list)
            {
                foreach (string strRng in seq.Ranges)
                {
                    Microsoft.Office.Interop.Excel.Worksheet wsht = getSheetObject(strRng) as
                        Microsoft.Office.Interop.Excel.Worksheet;
                    Microsoft.Office.Interop.Excel.Range rng = wsht.get_Range(a2, Type.Missing);
                    setVersionDate(wsht, a2);
                    p_kpi.Insert(p_filePath, p_user, strRng);
                }
            }
            //closeWorkBooksWithNoSave(p_filePath);
            Microsoft.Office.Interop.Excel.Workbook wbk = exlAPP.Workbooks[1];
            wbk.Save();
        }

        public void GetKPIAll(Service_KPI p_kpi, string p_file)
        {
            System.Data.DataTable dt = p_kpi.GetData();
            openFile(p_file);
            Microsoft.Office.Interop.Excel.Worksheet source = getSheetObject("Sheet1") as
                        Microsoft.Office.Interop.Excel.Worksheet;
            object obj = System.Reflection.Missing.Value;
            foreach (DataRow row in dt.Rows)
            {
                openFile(row["ppt"].ToString());
                Microsoft.Office.Interop.Excel.Worksheet wsht = getSheetObject(row["misgroup"].ToString()) as
                        Microsoft.Office.Interop.Excel.Worksheet;
                wsht.Select(System.Reflection.Missing.Value);
                wsht.Copy(obj, source);

            }
            Microsoft.Office.Interop.Excel.Workbook wbk = exlAPP.Workbooks[1];
            wbk.Save();
            closeWorkBooksWithNoSave(p_file);

        }

        public void GetCreditCardCount(Service_CreditCardCount p_credit
            , string p_file)
        {
            openFile(p_file);
            DataSet ds = p_credit.GetSummaryData();
            int x, y;
            y = 1;
            x = 1;
            Range rng = null;
            Worksheet wsht = null;
            for (int j = 0; j < 3; j++) //只有前三個會在sheet上
            {
                y = 1;
                x = 1;
                wsht = getSheetObject(p_credit.SheetName[j]) as Worksheet;
                System.Data.DataTable dt = ds.Tables[p_credit.SheetName[j]];

                y = 2;
                foreach (DataRow row in dt.Rows)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        rng = wsht.Cells[y, i + 1] as Range;
                        rng.Value2 = row[i].ToString();
                    }
                    y++;
                }
            }

            wsht = getSheetObject(p_credit.SheetName[2]) as Worksheet;
            for (int j = 3; j < p_credit.SheetName.Length; j++)
            {
                y = 2;

                System.Data.DataTable dt = ds.Tables[p_credit.SheetName[j]];
                foreach (DataRow row in dt.Rows)
                {
                    rng = wsht.Cells[y, 15 + j - 3] as Range;
                    rng.Value2 = row[1].ToString();
                    y++;
                }
            }

            Microsoft.Office.Interop.Excel.Workbook wbk = exlAPP.Workbooks[1];
            wbk.Save();
            closeWorkBooksWithNoSave(p_file);
        }
       
        public void GetCIB_DDTD(Service_CIB_DD_TD p_ddtd, string p_file
            , string p_year,string p_method)
        {
            //System.Reflection.MethodInfo mi = p_ddtd.GetType().GetMethod(
            //    p_method, new Type[] { typeof(System.String) });
            //DataSet ds = mi.Invoke(p_ddtd, new object[] { p_year }) as DataSet;
            //executeGetCIBDDTD(p_ddtd, ds, p_file); 
           Type[] paraType = new Type[] { typeof(string)};
           MethodInfo mi = Utility.FindMethodInfo(p_ddtd.GetType().GetMethods()
               , p_method, paraType);
           DataSet ds = mi.Invoke(p_ddtd, new object[] { p_year }) as DataSet;
           executeGetCIBDDTD(p_ddtd, ds, p_file); 
        }

        public void GetCIB_DDTD(Service_CIB_DD_TD p_ddtd, string p_file
                    , string[] p_year, string p_method)
        {
            //System.Reflection.MethodInfo mi = p_ddtd.GetType().GetMethod(
            //    p_method, new Type[] { typeof(System.String) });
            //DataSet ds = mi.Invoke(p_ddtd, new object[] { p_year }) as DataSet;
            //executeGetCIBDDTD(p_ddtd, ds, p_file);
            Type[] paraType = new Type[] { typeof(string[]) };
            MethodInfo mi = Utility.FindMethodInfo(p_ddtd.GetType().GetMethods()
                , p_method, paraType);
            DataSet ds = mi.Invoke(p_ddtd, new object[] { p_year }) as DataSet;
            executeGetCIBDDTD(p_ddtd, ds, p_file); 

        }


        public void GetCIB_DDTD(Service_CIB_DD_TD p_ddtd, string p_file
            , string p_year)
        {
            GetCIB_DDTD(p_ddtd, p_file, p_year, "GetSummaryData");
        }

        public void GetCIB_DDTD(Service_CIB_DD_TD p_ddtd, string p_file
            , string[] p_year)
        {
            System.Reflection.MethodInfo mi = p_ddtd.GetType().GetMethod(
                "GetSummaryData", new Type[] { typeof(System.String[]) });
            DataSet ds = mi.Invoke(p_ddtd, new object[] { p_year }) as DataSet;
            executeGetCIBDDTD(p_ddtd, ds, p_file);            
        }

        protected void executeGetCIBDDTD(Service_CIB_DD_TD p_ddtd
            ,DataSet p_ds,string p_file)
        {

            openFile(p_file);
            Worksheet wsht = null;
            //填入外幣的利息支出、FTP息
            wsht = getSheetObject(p_ddtd.GetDDSheetName()) as Worksheet;
            System.Data.DataTable dt = p_ds.Tables[p_ddtd.GetDDSheetName()];
            setTDDDValue(wsht, dt);

            dt = p_ds.Tables[p_ddtd.GetTDSheetName()];
            wsht = getSheetObject(p_ddtd.GetTDSheetName()) as Worksheet;
            setTDDDValue(wsht, dt);

            wsht = getSheetObject(p_ddtd.GetDDRateDiffSheetName()) as Worksheet;
            dt = p_ds.Tables[p_ddtd.GetDDOccupyName()];
            int iRow = setTDDDOccupy(wsht, dt);
            dt = p_ds.Tables[p_ddtd.GetDDFTPRateName()];
            iRow = setFTPRate(wsht, dt, iRow);
            dt = p_ds.Tables[p_ddtd.GetDDRateDiffSheetName()];
            setDDRateDiff(wsht, dt, iRow);

            wsht = getSheetObject(p_ddtd.GetTDRateDiffSheetName()) as Worksheet;
            dt = p_ds.Tables[p_ddtd.GetTDOccupyName()];
            iRow = setTDDDOccupy(wsht, dt);
            dt = p_ds.Tables[p_ddtd.GetTDFTPRateName()];
            iRow = setFTPRate(wsht, dt, iRow);
            dt = p_ds.Tables[p_ddtd.GetTDRateDiffSheetName()];
            setDDRateDiff(wsht, dt, iRow);

            Microsoft.Office.Interop.Excel.Workbook wbk = exlAPP.Workbooks[1];
            wbk.Save();
            closeWorkBooksWithNoSave(p_file);        
        }

        protected void setDDRateDiff(Worksheet p_sht, System.Data.DataTable p_dt
            , int p_row)
        {
            p_row++;
            Range rng = null;
            rng = p_sht.Cells[p_row, 1] as Range;
            rng.Value2 = "主要外幣利差";
            p_row++;
         
            for (int x = 0; x < p_dt.Rows.Count - 1; x++)
            {
                DataRow row = p_dt.Rows[x];
                //for (int i = 0; i < 13; i++)
                for (int i = 0; i < p_dt.Columns.Count; i++)
                {
                    rng = p_sht.Cells[p_row, i + 1] as Range;
                    rng.Value2 = row[i].ToString();
                    rng.NumberFormatLocal = "0.00%";
                }
                p_row++;
            }

        }

        protected int setFTPRate(Worksheet p_sht, System.Data.DataTable p_dt
            , int p_row)
        {
            p_row++;
            Range rng = null;
            rng = p_sht.Cells[p_row, 1] as Range;
            rng.Value2 = "全部外幣存款";
            p_row++;
            rng = p_sht.Cells[p_row, 1] as Range;
            rng.Value2 = "FTP率";
            int x = 2;
            for (int i = 5; i < p_dt.Columns.Count; i = i + 5)
            {
                rng = p_sht.Cells[p_row, x] as Range;
                rng.Value2 = p_dt.Rows[0][i].ToString();
                rng.NumberFormatLocal = "0.00%";
                x++;
            }

            p_row++;
            rng = p_sht.Cells[p_row, 1] as Range;
            rng.Value2 = "成本率";
            x = 2;
            for (int i = 3; i < p_dt.Columns.Count; i = i + 5)
            {
                rng = p_sht.Cells[p_row, x] as Range;
                rng.Value2 = p_dt.Rows[0][i].ToString();
                rng.NumberFormatLocal = "0.00%";
                x++;
            }

            p_row++;
            rng = p_sht.Cells[p_row, 1] as Range;
            rng.Value2 = "利差率";
            x--; //因為離開了迴圈時會多跑一次
            for (int i = 1; i < x ; i++)
            {
                if (i > 0)
                {
                    rng = p_sht.Cells[p_row, i + 1] as Range;
                    rng.FormulaR1C1 = "=R[-2]C-R[-1]C";
                }
            }

            return p_row++;
        }

        /// <summary>
        /// 填入佔比
        /// </summary>
        /// <param name="p_sht"></param>
        /// <param name="p_dt"></param>
        /// <returns></returns>
        protected int setTDDDOccupy(Worksheet p_sht, System.Data.DataTable p_dt)
        {
            Range rng = null;
            int x, y;
            y = 1;
            x = 1;
            foreach (DataColumn col in p_dt.Columns)
            {
                rng = p_sht.Cells[y, x] as Range;
                rng.Value2 = col.ColumnName;
                x++;
            }
            y++;
            bool isAVGBAL = false;
            foreach (DataRow row in p_dt.Rows)
            {

                isAVGBAL = false;
                for (int i = 0; i < p_dt.Columns.Count; i++)
                {
                    rng = p_sht.Cells[y, i + 1] as Range;
                    rng.Value2 = DBNull.Value.Equals(row[i]) ? " "
                        : row[i].ToString();

                    if (rng.Value2.Equals("月均額"))
                    {
                        isAVGBAL = true;
                    }

                    if (isAVGBAL.Equals(true))
                    {
                        rng.NumberFormatLocal = "#,##0_ ";
                    }
                    else
                    {
                        rng.NumberFormatLocal = "0.00%";
                    }

                }
                y++;
            }
            rng = p_sht.Cells[y, 1] as Range;
            rng.Value2 = "與上月比較均額";

            for (int i = 0; i < p_dt.Columns.Count; i++)
            {
                if (i > 0)
                {
                    rng = p_sht.Cells[y, i + 1] as Range;
                    //=R[-1]C-R[-1]C[-1]
                    //rng.Value2 = "=R[-1]C-R[-1]C[1]";
                    if (i > 1)
                    {
                        rng.Value2 = "=R[-1]C-R[-1]C[-1]";
                    }

                }
            }

            return y++;
        }

        protected void setTDDDValue(Worksheet p_sht, System.Data.DataTable p_dt)
        {
            string formulaR1C1, AVG_BAL_PER;
            formulaR1C1 = "=RC[-1]/R[{0}]C[-1]";
            AVG_BAL_PER = "AVG_BAL_PER";
            Range rng = null;

            int x, y, z;
            y = 1;
            x = 1;
            z = p_dt.Rows.Count - 1;
            foreach (DataColumn col in p_dt.Columns)
            {
                rng = p_sht.Cells[y, x] as Range;
                rng.Value2 = col.ColumnName;
                x++;
            }
            y++;

            foreach (DataRow row in p_dt.Rows)
            {
                for (int i = 0; i < p_dt.Columns.Count; i++)
                {
                    rng = p_sht.Cells[y, i + 1] as Range;
                    //201012AVG_BAL_PER
                    //NumberFormatLocal = "#,##0_ " 千分號
                    //NumberFormatLocal = "0.00%"

                    if (p_dt.Columns[i].ToString().IndexOf(AVG_BAL_PER) > 0)
                    {
                        rng.FormulaR1C1 = string.Format(formulaR1C1, z.ToString());

                    }
                    else
                    {
                        rng.Value2 = row[i].ToString().Equals("ZZZ") ? ""
                            : row[i].ToString();
                    }

                    if (p_dt.Columns[i].ToString().IndexOf("PER") > 0)
                    {
                        rng.NumberFormatLocal = "0.00%";
                    }
                    else
                    {
                        if (i > 0)
                        {
                            rng.NumberFormatLocal = "#,##0_ ";
                        }
                    }
                }
                y++;
                z--;
            }
        }

        public void WMG005(Service_WMG005 p_wmg, string p_file, string p_ym
            , Service_WMG005.DepositOpt p_opt)
        {
            openFile(p_file);
            int iRow;
            iRow = 5;
            Range rng = null;
            string[] rates, columns;
            rates = new[] { "固定", "機動", "小計" };
            columns = p_wmg.GetColumns(p_opt);
            string cell, col;
            Worksheet sht = getSheetObject("WMG005") as Worksheet;
            IQueryable<string> dueDateCollect = p_wmg.GetDistinctDueDate();
            rng = sht.get_Range("A2", Type.Missing);
            rng.Value2 = p_wmg.GetCellYM(p_ym);
            foreach (string dueDate in dueDateCollect)
            {
                //月份：100年3月份
                col = "A";
                cell = string.Format("{0}{1}", col, iRow.ToString());
                rng = sht.get_Range(cell, Type.Missing);
                if (dueDate.Substring(0, 1).Equals("2"))
                {
                    rng.Value2 = string.Format("{0}{1}", Convert.ToString(
                        Convert.ToInt16(dueDate.Substring(0, 4)) - 1911)
                        , dueDate.Substring(4));
                }
                else
                {
                    rng.Value2 = dueDate;
                }

                foreach (string rate in rates)
                {
                    IQueryable<DataRow> selectRow = p_wmg.GetSelectRow(dueDate, rate);
                    if (selectRow.Count().Equals(0)) //不是0
                    { }
                    else
                    {
                        foreach (DataRow row in selectRow)
                        {
                            for (int i = 2; i < row.Table.Columns.Count; i++)
                            {
                                cell = string.Format("{0}{1}", columns[i], iRow.ToString());
                                rng = sht.get_Range(cell, Type.Missing);
                                rng.Value2 = row[i].ToString();
                            }
                        }
                    }
                    iRow++;
                }
            }
            rng = sht.get_Range("A5", Type.Missing);
            rng.Value2 = "已到期";
            Workbook wbk = exlAPP.Workbooks[1];
            wbk.Save();
            closeWorkBooksWithNoSave(p_file);
        }

        public void WMGStockTopTen(System.Data.DataTable p_dt,string p_file
            ,string[] p_months)
        {
            openFile(p_file);
            /*
             * B3 基金名
             * C1 月份 G1 有4個Range 
             * C2：銷量
             * D2：贖回 改由EXCEL 的公式來計算，而不在SQL中產生
             * E2：庫存成本
             * F2：報酬率
             */
            string shtNmae;
            shtNmae = "庫存前十大排名";
            Worksheet sht = getSheetObject(shtNmae) as Worksheet;
            //先把年月給填入吧
            int y, x;
            y = 1;
            x = 3;
            Range rng = null;
            foreach (string mon in p_months)
            {
                rng = sht.Cells[y, x] as Range;
                rng.Value2 = mon;
                x += 4;
            }
            y = 3;            
            foreach (DataRow row in p_dt.Rows)
            {
                x = 2;
                foreach (DataColumn col in p_dt.Columns)
                {
                    if (col.ColumnName.IndexOf("贖回") < 0)
                    {
                        rng = sht.Cells[y, x] as Range;
                        rng.Value2 = DBNull.Value.Equals(row[col]) ? "" : row[col].ToString();
                    }                    
                    x++;
                }
                y++;            
            }
            Workbook wbk = exlAPP.Workbooks[1];
            wbk.Save();
            closeWorkBooksWithNoSave(p_file);
        }
       
    }


}
